#delimit;
clear;
capture log close;
set more off;

local in "/data";
local pathtables "/data";
local pathtab "/data";

local out "/data";

use "`in'/rollup_merged_97_14.dta", clear;
sort pid year;

drop teditseq97 teditseq99 teditseq01 person teditseq03 teditseq04 permid PERMID_201006  PERMID_201206  PERMID_201406;
rename long llong;
ren PCTIN903 pctin903;
ren int interest;
label var t "t=1 if data comes from 2010/06 (new data) and 0 if from 2010/12 (old data)";
/*Drop old 2010 data*/
drop if t==0;


keep pid year dtopen dtclo loantype hicredit_tl;
keep if loantype=="SECG" | loantype=="INST" | loantype=="AUTO" | loantype=="MORT"; 

/*******Age Variables********/;
merge m:1 pid using "`pathtab'/age_tomerge.dta";
drop if _m==2;
drop _merge;

/*Some hicredit_tl are missing (4%). I will drop those for which dtopen=dtclo*/
*drop if hicredit_tl==. & dtopen-dtclo==0
/*Some hicredit_tl are missing (4%). I will drop them*/
drop if hicredit_tl==.;

*gen aa=1;

/*Some loans are duplicated, usually because they have been reported for several years*/
sort pid loantype dtopen hicredit_tl year;
*by pid loantype dtopen hicredit_tl: egen sumaa=sum(aa);
/*I will keep the one that was report last. Earlier records might have dtclo missing or 0 if the loan had not been closed yet. The latest point will have the most up-to-date info*/
by pid loantype dtopen hicredit_tl: keep if _n==_N;

#delimit;
gen dtopen_temp=string(dtopen)+string(28);
gen dtopen_st=date(dtopen_temp,"YMD");
format dtopen_st %td;
gen month=month(dtopen_st);
drop dtopen_st;

gen dtopen_temp2=string(dtopen)+string(30) if month==4 | month==6 | month==9 | month==11;

replace dtopen_temp2=string(dtopen)+string(31) if month==1 | month==3 | month==5 | month==7 | month==8 | month==10 | month==12;
replace dtopen_temp2=string(dtopen)+string(28) if month==2;
gen dtopen_st=date(dtopen_temp2,"YMD");
format dtopen_st %td;

drop dtopen_temp dtopen_temp2 month;

replace loantype="install" if loantype=="INST";
replace loantype="auto" if loantype=="AUTO";
replace loantype="secgoods" if loantype=="SECG";
replace loantype="mortgage" if loantype=="MORT";


local var="auto install secgoods mortgage";
foreach j of local var {;
	preserve; 
	keep if loantype=="`j'";
	forvalues age=22/32 {;
		bys pid: egen `j'_hicredit_`age'=sum(hicredit_tl) if loantype=="`j'" & (dtopen_st-dob)/365<=`age'+1;
		/*Expressing debt in $1,000*/
		replace `j'_hicredit_`age'=`j'_hicredit_`age'/1000;
	};
	sort pid year;
	by pid: keep if _n==1;
	keep pid `j'_hicredit_*;
	save "`out'/temp_`j'_hicredit.dta", replace;
	restore;
};

use "`out'/temp_auto_hicredit.dta", clear;
merge 1:1 pid using "`out'/temp_install_hicredit.dta";
drop _merge;
merge 1:1 pid using "`out'/temp_secgoods_hicredit.dta";
drop _merge;
merge 1:1 pid using "`out'/temp_mortgage_hicredit.dta";
drop _merge;
compress;
save "`out'/hicredit_tomerge.dta", replace;

erase "`out'/temp_auto_hicredit.dta";
erase "`out'/temp_install_hicredit.dta";
erase "`out'/temp_secgoods_hicredit.dta";
erase "`out'/temp_mortgage_hicredit.dta";
